using System;
using System.Collections.Generic;
using System.Text;
using Sinacor.Infra.Service.Data;
using Sinacor.Infra.Service.Security.Authorization;
using System.Data.Common;
using System.Data;
using Sinacor.Servicos.Comum.Util.SGBD;
using Sinacor.Servico.Bovespa.BovespaBase.Util; 
using Sinacor.Servico.Bovespa.Ordens;


namespace Sinacor.Servico.Bovespa.Ordens.ResourceAccess
{
    public class Justificativa : BaseResourceAccess
    {
        public Justificativa()
            : base(AuthorizationContext.Current.CompanyId)
        {

        }

        /// <summary>
        /// Servico responsavel por Inserir uma Justificativa
        /// Historico:
        /// Data: 26/12/2007
        /// Autor: Ludmilla Modesto 
        /// Observacao: Criacao da Operacao
        /// 
        /// Data : 20/02/2008
        /// Autor: Eduardo Sertorio
        /// Obs. : Incluido retorno da quantidade de linhas - eduardo sertorio - 200802201214
        /// </summary>
        /// <param name="request"></param>
        /// <returns>int</returns>
        public int InserirJustificativa(BusinessEntity.Justificativa request)
        {
            StringBuilder builder = new StringBuilder();

            builder.AppendLine(" declare ");
            builder.AppendLine(" V_nSeq  torjusd.nr_seq%type; ");
            builder.AppendLine(" V_dtSis  date := sysdate; ");
            builder.AppendLine(" V_dtAlt  date; ");
            builder.AppendLine(" V_rRowid rowid; ");
            builder.AppendLine(" V_nInJust torctrl_jusd.in_justificativa%type; ");
            builder.AppendLine(" V_sAux  varchar2(10); ");
            builder.AppendLine(" cursor jus is ");
            builder.AppendLine(" select nvl( max( nr_seq ), 0 ) ");
            builder.AppendLine(" from torjusd ");
            builder.AppendLine(" where  ");
            builder.AppendLine(" dt_datord = :DataOrdem ");
            builder.AppendLine(" and nr_seqord = :NumeroOrdem ");
            builder.AppendLine(" and nr_subseq = :SubsequenciaOrdem; ");
            builder.AppendLine(" cursor ctrl ( V_dtSis date ) is ");
            builder.AppendLine(" select  ");
            builder.AppendLine(" a.rowid,  ");
            builder.AppendLine(" a.in_justificativa,  ");
            builder.AppendLine(" a.dt_sistema ");
            builder.AppendLine(" from  ");
            builder.AppendLine(" torctrl_jusd a, ");
            builder.AppendLine(" torlogd b, ");
            builder.AppendLine(" torlogd c ");
            builder.AppendLine(" where  ");
            builder.AppendLine(" a.dt_datord  = :DataOrdem ");
            builder.AppendLine(" and a.nr_seqord  = :NumeroOrdem ");
            builder.AppendLine(" and a.nr_subseq  = :SubsequenciaOrdem ");
            builder.AppendLine(" and a.cd_cliente_atua = :CodigoCliente ");
            builder.AppendLine(" and a.dt_sistema <= V_dtSis ");
            builder.AppendLine(" and substr(to_char( 10000000000 + a.in_justificativa ),11,1) = '0' ");
            builder.AppendLine(" and b.dt_datord  = a.dt_datord ");
            builder.AppendLine(" and b.nr_seqord  = a.nr_seqord ");
            builder.AppendLine(" and b.nr_subseq  = a.nr_subseq ");
            builder.AppendLine(" and b.dt_datlog  = a.dt_sistema ");
            builder.AppendLine(" and b.in_situac  = 'B' ");
            builder.AppendLine(" and b.cd_cliente = a.cd_cliente_ori ");
            builder.AppendLine(" and c.dt_datord  = a.dt_datord ");
            builder.AppendLine(" and c.nr_seqord  = a.nr_seqord ");
            builder.AppendLine(" and c.nr_subseq  = a.nr_subseq ");
            builder.AppendLine(" and c.dt_datlog  = a.dt_sistema ");
            builder.AppendLine(" and c.in_situac  = 'C' ");
            builder.AppendLine(" and c.cd_cliente = a.cd_cliente_atua ");
            builder.AppendLine(" for update of a.in_justificativa ");
            builder.AppendLine(" order by a.dt_sistema desc; ");
            builder.AppendLine(" begin ");
            builder.AppendLine(" open jus; ");
            builder.AppendLine(" fetch jus into V_nSeq; ");
            builder.AppendLine(" close jus; ");
            builder.AppendLine(" open ctrl ( V_dtSis ); ");
            builder.AppendLine(" fetch ctrl into V_rRowid, V_nInJust, V_dtAlt; ");
            builder.AppendLine(" if ctrl%found then ");
            builder.AppendLine(" V_sAux := substr( to_char( 10000000000 + V_nInJust ), 2, 10 ); ");
            builder.AppendLine(" V_sAux := substr( V_sAux, 1, 9 ) || '1'; ");
            builder.AppendLine(" V_nInJust := to_number( V_sAux ); ");
            builder.AppendLine(" update torctrl_jusd ");
            builder.AppendLine(" set in_justificativa = V_nInJust ");
            builder.AppendLine(" where rowid = V_rRowid; ");
            builder.AppendLine(" end if; ");
            builder.AppendLine(" close ctrl; ");
            builder.AppendLine(" V_nSeq := V_nSeq + 1; ");
            builder.AppendLine(" insert into torjusd ");
            builder.AppendLine(" ( DT_DATORD, ");
            builder.AppendLine(" NR_SEQORD,  ");
            builder.AppendLine(" NR_SUBSEQ, ");
            builder.AppendLine(" DT_HORORD, ");
            builder.AppendLine(" NR_SEQ,   ");
            builder.AppendLine(" DT_SISTEMA, ");
            builder.AppendLine(" NM_JUSTIF, ");
            builder.AppendLine(" DT_ALTERA, ");
            builder.AppendLine(" CD_USUARIO, ");
            builder.AppendLine(" CD_TERMINAL, ");
            builder.AppendLine(" DT_JUSTIFICA ) ");
            builder.AppendLine(" values ");
            builder.AppendLine(" ( :DataOrdem,  ");
            builder.AppendLine(" :NumeroOrdem,  ");
            builder.AppendLine(" :SubsequenciaOrdem,  ");
            builder.AppendLine(" :DataHoraOrdem, ");
            builder.AppendLine(" V_nSeq,  ");
            builder.AppendLine(" V_dtSis,   ");
            builder.AppendLine(" :TextoJustificativa,  ");
            builder.AppendLine(" V_dtAlt, ");
            builder.AppendLine(" :UsuarioConectado,  ");
            builder.AppendLine(" :NomeEstacao,   ");
            builder.AppendLine(" SYSDATE ); ");
            builder.AppendLine(" end; ");

            DbCommand dbCommand = this.DataBase.GetSqlStringCommand(builder.ToString());

            this.DataBase.AddInParameter(dbCommand, "NumeroOrdem", DbType.Int32, request.Ordem.NumeroOrdem);
            this.DataBase.AddInParameter(dbCommand, "SubsequenciaOrdem", DbType.Int32, request.Ordem.NumeroOrdemSubSequencia);
            this.DataBase.AddInParameter(dbCommand, "DataOrdem", DbType.DateTime, request.Ordem.DataOrdem);
            this.DataBase.AddInParameter(dbCommand, "TextoJustificativa", DbType.String, request.TextoJustificativa);
            this.DataBase.AddInParameter(dbCommand, "CodigoCliente", DbType.Int32, request.Ordem.CodigoCliente);
            this.DataBase.AddInParameter(dbCommand, "DataHoraOrdem", DbType.DateTime, request.Ordem.HoraOrdem);
            this.DataBase.AddInParameter(dbCommand, "UsuarioConectado", DbType.Int32, Sinacor.Infra.Service.Security.Authorization.AuthorizationContext.Current.UserId);            
            this.DataBase.AddInParameter(dbCommand, "NomeEstacao", DbType.String, Sinacor.Infra.Service.Security.Authorization.AuthorizationContext.Current.ClientHost.Name);
            
            return this.DataBase.ExecuteNonQuery(dbCommand); //incluido retorno da quantidade de linhas - eduardo sertorio - 200802201214
        }

        /// <summary>
        /// Servico responsavel por Atualizar uma Justificativa
        /// Historico:
        /// Data: 26/12/2007
        /// Autor: Ludmilla Modesto 
        /// Observacao: Criacao da Operacao
        /// 
        /// Data : 20/02/2008
        /// Autor: Eduardo Sertorio
        /// Observacao: Alteracao do retorno de void para int para validar a quantidade de linhas alteradas
        /// </summary>
        /// <param name="request">BusinessEntity.Justificativa</param>
        /// <returns>int</returns>
        public int AlterarJustificativa(BusinessEntity.Justificativa request)
        {
            StringBuilder builder = new StringBuilder();

            builder.AppendLine(" Update TORJUSD  ");
            builder.AppendLine(" Set NM_JUSTIF    = :TextoJustificativa,  ");
            builder.AppendLine(" CD_USUARIO   = :UsuarioConectado,  ");
            builder.AppendLine(" CD_TERMINAL  = :NomeEstacao,   ");
            builder.AppendLine(" DT_JUSTIFICA = sysdate,  ");
            builder.AppendLine(" DT_ALTERA = sysdate  ");
            builder.AppendLine(" where   ");
            builder.AppendLine(" DT_DATORD = :DataOrdem  ");
            builder.AppendLine(" and NR_SEQORD = :NumeroOrdem  ");
            builder.AppendLine(" and NR_SUBSEQ = :SubsequenciaOrdem  ");
            builder.AppendLine(" and NR_SEQ    = :IdentificadorJustificativa  ");

            DbCommand dbCommand = this.DataBase.GetSqlStringCommand(builder.ToString());

            this.DataBase.AddInParameter(dbCommand, "NumeroOrdem", DbType.Int32, request.Ordem.NumeroOrdem);
            this.DataBase.AddInParameter(dbCommand, "SubsequenciaOrdem", DbType.Int32, request.Ordem.NumeroOrdemSubSequencia);
            this.DataBase.AddInParameter(dbCommand, "DataOrdem", DbType.DateTime, request.Ordem.DataOrdem);
            this.DataBase.AddInParameter(dbCommand, "TextoJustificativa", DbType.String, request.TextoJustificativa);
            this.DataBase.AddInParameter(dbCommand, "IdentificadorJustificativa", DbType.Int32, request.IdentificadorJustificativa);
            this.DataBase.AddInParameter(dbCommand, "UsuarioConectado", DbType.Int32, AuthorizationContext.Current.UserId);
            this.DataBase.AddInParameter(dbCommand, "NomeEstacao", DbType.String, AuthorizationContext.Current.ClientHost.Name);

            return this.DataBase.ExecuteNonQuery(dbCommand);
        }

        /// <summary>
        /// Servico responsavel por Consultar uma Justificativa
        /// Historico:
        /// Data: 26/12/2007
        /// Autor: Ludmilla Modesto 
        /// Observacao: Criacao da Operacao
        /// 
        /// Data : 22/02/2008
        /// Autor: Eduardo Sertorio
        /// Obs. : Alterar teste do response verificando Count>0 para evidenciar pesquisa sem resultado
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public List<BusinessEntity.Justificativa> ConsultarJustificativas(BusinessEntity.Justificativa request)
        {
            BusinessEntity.Justificativa beJustificativa = null;
            List<BusinessEntity.Justificativa> response = new List<BusinessEntity.Justificativa>();
            StringBuilder builder = new StringBuilder();

            builder.AppendLine(" select   ");
            builder.AppendLine(" TORJUSD.NM_JUSTIF, ");
            builder.AppendLine(" TORJUSD.NR_SEQ, ");
            builder.AppendLine(" TORJUSD.DT_ALTERA ");
            builder.AppendLine(" from   ");
            builder.AppendLine(" TORJUSD   ");
            builder.AppendLine(" where   ");
            builder.AppendLine(" TORJUSD.DT_DATORD = :DataOrdem  ");
            builder.AppendLine(" and TORJUSD.NR_SEQORD = :NumeroOrdem  ");
            builder.AppendLine(" and TORJUSD.NR_SUBSEQ = :SubsequenciaOrdem  ");
            builder.AppendLine(" order by TORJUSD.NR_SEQ, TORJUSD.DT_ALTERA  ");

            DbCommand dbCommand = this.DataBase.GetSqlStringCommand(builder.ToString());

            this.DataBase.AddInParameter(dbCommand, "NumeroOrdem", DbType.Int32, request.Ordem.NumeroOrdem);
            this.DataBase.AddInParameter(dbCommand, "SubsequenciaOrdem", DbType.Int32, request.Ordem.NumeroOrdemSubSequencia);
            this.DataBase.AddInParameter(dbCommand, "DataOrdem", DbType.DateTime, request.Ordem.DataOrdem);

            IDataReader readerJustificativa = this.DataBase.ExecuteReader(dbCommand);

            while (readerJustificativa.Read())
            {
                beJustificativa = new BusinessEntity.Justificativa();

                beJustificativa.TextoJustificativa = Sinacor.Servicos.Comum.Util.SGBD.Funcoes.BDValorString(readerJustificativa[BusinessEntity.Justificativa.ConsultarJustificativa.TextoJustificativa]);
                beJustificativa.IdentificadorJustificativa = Sinacor.Servicos.Comum.Util.SGBD.Funcoes.BDValorInt32(readerJustificativa[BusinessEntity.Justificativa.ConsultarJustificativa.IdentificadorJustificativa]);
                beJustificativa.DataAlteracao = Sinacor.Servicos.Comum.Util.SGBD.Funcoes.BDValorDateTime(readerJustificativa[BusinessEntity.Justificativa.ConsultarJustificativa.DataAlteracaoJustificativa]);

                response.Add(beJustificativa);
            }

            readerJustificativa.Close(); //incluido - esertorio - 200804291051 - ref issue ID0003836

            if (response != null && response.Count > 0) //incluido teste response.Count > 0 - eduardo sertorio - 200802221205
                return response;
            else
                return null;
        }

    }
}
